# -*- coding: utf-8 -*-
# This code written by ChanGOGOGO
"""
导出未接通名单
"""
import json
from re import S
import sys
import os

current_script_dir = os.path.dirname(os.path.abspath(__file__))
parent_dir = os.path.dirname(current_script_dir)
if parent_dir not in sys.path:
    sys.path.append(parent_dir)
import pymysql
from util import (
    query_es,
    logging,
    write_excel,
    date_slice,
    time,
    ms2min,
    data2beautify,
    db_ip,
    db_port,
    db_username,
    db_password,
    excel_save_dir,
)


class ExportNotConnectedPhone:
    def __init__(self, ui_data):
        self.data_date = ui_data["data_date"]
        self.business = ui_data.get("business")
        self.data_type = ui_data.get("data_type")
        self.call_type = ui_data.get("call_type")
        self.start_time = ui_data.get("start_time")
        self.end_time = ui_data.get("end_time")
        self.scene_names = ui_data.get("target_datas")
        company_id = ui_data.get("company_id")
        self.db_name = f"yicall_{company_id}"
        self.table_index = ui_data.get("business_id")
        self.es_index = (
            f"{self.db_name}_{self.table_index}_callout_session_ach_{self.data_date}"
        )
        self.conn = pymysql.connect(
            host=db_ip,
            port=db_port,
            user=db_username,
            passwd=db_password,
            charset="utf8",
        )
        self.cursor = self.conn.cursor()
        self.scene_info = self.query_scene()
        self.cursor.close()
        self.conn.close()

    def query_scene(self):
        """查询场景id及变量"""
        if len(self.scene_names) > 1:
            sql = f"""select a.variable_name,a.scene_id,b.scene_name from {self.db_name}.b_{self.table_index}_dialog_variable a LEFT JOIN {self.db_name}.b_{self.table_index}_call_scene b on a.scene_id=b.scene_id where a.variable_type="custom" AND b.scene_name in {tuple(self.scene_names)};"""
        else:
            sql = f"""select a.variable_name,a.scene_id,b.scene_name from {self.db_name}.b_{self.table_index}_dialog_variable a LEFT JOIN {self.db_name}.b_{self.table_index}_call_scene b on a.scene_id=b.scene_id where a.variable_type="custom" AND b.scene_name="{self.scene_names[0]}";"""
        logging.info(f"场景信息查询语句：{sql}")
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        scene_info = {}
        for x in result:
            variable_name, scene_id, scene_name = x
            find_scene_name = scene_info.get(scene_name)
            if find_scene_name is None:
                scene_info[scene_name] = {
                    "scene_id": scene_id,
                    "variable_name": [variable_name],
                }
            else:
                scene_info[scene_name]["variable_name"].append(variable_name)
        logging.info(f"场景信息：{data2beautify(scene_info)}")
        return scene_info

    def query_es_data(self, scene_id):
        """查询ES数据"""
        sql = {
            "query": {
                "bool": {
                    "must_not": [{"term": {"calloutConnectStatus": "100"}}],
                    "must": [
                        {"term": {"isLastCall": True}},
                        {"term": {"sceneId": scene_id}},
                        {
                            "range": {
                                "calloutFinishTime": {
                                    "gte": self.start_time,
                                    "lte": self.end_time,
                                }
                            }
                        },
                    ],
                }
            }
        }
        field = ["customerCaseId", "dialNumber", "detailInfo"]
        es_data = query_es(self.es_index, sql, field)
        return es_data

    def handle_data(self, es_data, scene_name):
        """处理数据"""
        scene_variable = self.scene_info[scene_name]["variable_name"]
        not_connected_phones = []
        for x in es_data:
            phone_info = []
            customer_case_id = x.get("customerCaseId")
            dial_number = x.get("dialNumber")
            detail_info = x.get("detailInfo")
            phone_info.append(customer_case_id)
            phone_info.append(dial_number)
            customer_variable = []
            try:
                for variable_name in scene_variable:
                    variable_value = detail_info.get(variable_name, "")
                    customer_variable.append(variable_value)
            except Exception as e:
                logging.error(f"处理数据异常：{e},{x}")
            phone_info.extend(customer_variable)
            not_connected_phones.append(phone_info)
        return not_connected_phones

    def export(self):
        excel_names = []
        for scene_name in self.scene_names:
            current_scene_id = self.scene_info[scene_name]["scene_id"]
            current_scene_variable = self.scene_info[scene_name]["variable_name"]
            current_scene_es_data = self.query_es_data(current_scene_id)
            if len(current_scene_es_data) < 1:
                logging.warning(
                    f"⚠️ 场景{scene_name}在{self.start_time}至{self.end_time}期间没有查询到未接通数据！"
                )
            else:
                current_scene_not_connected_phones = self.handle_data(
                    current_scene_es_data, scene_name
                )
                # 生成单个场景excel标题
                excel_title = ["CaseId", "电话号码"]
                for variable in current_scene_variable:
                    excel_title.append(variable)
                # 生成单个场景excel数据
                excel_data = []
                for x in current_scene_not_connected_phones:
                    excel_data.append(x)
                sheet_name = "未接通名单"
                file_name = f"{scene_name}_{self.data_type}"
                sheet_infos = [[sheet_name, excel_title, excel_data]]
                excel_name = write_excel(sheet_infos, excel_save_dir, file_name)
                excel_names.append(excel_name)
        if len(excel_names) > 0:
            return {"code": 0, "excel_names": excel_names, "msg": None}
        else:
            msg = (
                f"<font color=green>无法导出“{self.business}”的【{self.data_type}】，"
                f"原因：\n在{self.start_time}至{self.end_time}没有{self.call_type}数据！</font>"
            )
            return {"code": 1, "excel_names": excel_names, "msg": msg}


if __name__ == "__main__":
    ui_date = {
        "target_datas": ["UI_实验组-20250812102941-子场景", "常规测试-B"],
        "data_date": "202508",
        "business": "call2",
        "company_id": 1000,
        "business_id": 1001,
        "data_type": "未接通名单",
        "call_type": "呼出",
        "task_type": "活跃任务",
        "start_time": "2025-08-18 00:00:00",
        "end_time": "2025-08-18 23:59:59",
    }
    export_not_connected_phone = ExportNotConnectedPhone(ui_date)
    export_not_connected_phone.export()
